import { Inject, Provide } from '@midwayjs/core';
import { query, transaction } from '../dbConnPool/mariadb'; // 引入查询函数
import { Context } from '@midwayjs/koa';
import { uuidv7 } from 'uuidv7';
import { CachingFactory, MidwayCache } from '@midwayjs/cache-manager';
import { InjectClient } from '@midwayjs/core';
@Provide()
export class RecordDao {
  @Inject()
  ctx: Context;
  //创建患者信息
  @InjectClient(CachingFactory, 'redis')
  cache: MidwayCache; //redis缓存
  //创建记录
  async createRecord(patientId: string) {
    const userId = this.ctx.state.user.userId;
    const recordId = uuidv7();
    return transaction(async conn => {
      const sql =
        'INSERT INTO record (id, user_id,patient_id, created_at) VALUES (?, ?, ?, NOW())';
      const sqlParams = [recordId, userId, patientId];
      await query(sql, sqlParams, false, conn);
      const sql3 = `
        INSERT INTO department_daily_statistics (department_id, date, new_records)
            VALUES (?,CURDATE(), 1)
            ON DUPLICATE KEY UPDATE new_records = new_records + 1;`;
      const sqlParams3 = [this.ctx.state.user.departmentId];
      await query(sql3, sqlParams3, false, conn);
      return recordId;
    });
  }
  //查询记录是否存在
  async getRecordById(id: string) {
    const sql = `
        SELECT
            COUNT(*) AS count
        FROM 
            record 
        WHERE 
            old_id = ? AND valid_flag = 1
        `;
    const sqlParams = [id];
    const result = await query(sql, sqlParams, false);
    return result[0].count;
  }
  //查询患者记录
  async getRecordHistory(patientId: string) {
    const sql = `
        SELECT
            id AS recordId,
            created_at AS createdAt
        FROM
            record
        WHERE
            patient_id = ? AND valid_flag = 1
        ORDER BY
            created_at DESC
        `;
    const sqlParams = [patientId];
    return await query(sql, sqlParams, false);
  }
  // 查询患者记录详情
  async getRecordDetails(recordId: string) {
    return transaction(async conn => {
      const recordSql = `
      SELECT
        u.name AS userName,
        i.item_label AS identity,
        r.patient_id AS patientId,
        r.created_at AS createdAt,
        p.name AS patientName,
        d.name AS departmentName,
        p.age,
        p.visit_card_id AS visitCardId,
        s.item_label AS sex,
        p.phone
      FROM
        record r
      JOIN
        patient p ON r.patient_id = p.id
      JOIN
        user u ON r.user_id = u.user_id
      JOIN
        department d ON p.department_id = d.id  
      JOIN
        sys_dict_item s ON p.sex = s.item_code AND s.dict_code = 'SEX'  
      JOIN
      sys_dict_item i ON u.identity = i.item_code AND i.dict_code = 'IDENTITY'  
      WHERE
        r.id = ? AND r.valid_flag = 1;
    `;
      const recordParams = [recordId];
      const recordResult = await query(recordSql, recordParams, false, conn);
      const record = recordResult[0];
      const patientId = record.patientId;
      if (!record) {
        return null;
      }
      // 查询血液检查数据
      const bloodSql = `
      SELECT
            RBC,
            HGB,
            HCT,
            MCV,
            MCH,
            MCHC,
            \`RDW-SD\`,
            b.created_at AS createdAt
        FROM
            blood_drooping b
        JOIN
            record r ON b.record_id = r.id
        WHERE
            b.patient_id = ? AND b.valid_flag = 1 AND r.valid_flag = 1 ;
    `;
      const bloodParams = [patientId];
      const bloodResult = await query(bloodSql, bloodParams, false, conn);
      record.bloodDrooping = bloodResult;
      // 查询图片信息
      const imgSql = `
      SELECT
        i.id AS imgId,
        i.link,
        i.total,
        id.type,
        id.quantity,
        id.percentage,
        id.link AS detailLink
      FROM
        img i
      LEFT JOIN
        img_detail id ON i.id = id.img_id
      WHERE
        i.record_id = ? AND i.valid_flag = 1;
    `;
      const imgParams = [recordId];
      const imgResult = await query(imgSql, imgParams, false, conn);
      const imgMap = new Map();
      imgResult.forEach(img => {
        if (!imgMap.has(img.imgId)) {
          imgMap.set(img.imgId, {
            originalImageUrl: img.link,
            total: img.total,
            createdAt: img.imgCreatedAt,
            details: []
          });
        }
        if (img.type) {
          imgMap.get(img.imgId).details.push({
            type: img.type,
            quantity: img.quantity,
            percentage: img.percentage,
            imgPath: img.detailLink
          });
        }
      });
      record.imgDetail = Array.from(imgMap.values());

      // 查询报告信息
      const reportSql = `
      SELECT
        outcome,
        text
      FROM
        report
      WHERE
        record_id = ? AND valid_flag = 1;
    `;
      const reportParams = [recordId];
      const reportResult = await query(reportSql, reportParams, false, conn);
      record.report = reportResult[0];

      return record;
    });
  }
  //删除记录
  async deleteRecord(id: string) {
    const sql = `
        UPDATE record SET valid_flag = 0 WHERE id = ?;
        `;
    const sqlParams = [id];
    await query(sql, sqlParams, false)
    return true;
  }
  //查找历史记录
  async getBloodRecordHistory(patientId: string) {
    const sql = `
          SELECT
            b.record_id AS recordId,
            b.patient_id AS patientId,
            RBC,
            HGB,
            HCT,
            MCV,
            MCH,
            MCHC,
             \`RDW-SD\`,
            b.created_at AS createdAt
        FROM
            blood_drooping b
        JOIN
            record r ON b.record_id = r.id
        WHERE
            b.patient_id = ? AND b.valid_flag = 1 AND r.valid_flag = 1 ;
        `;
    const sqlParams = [patientId];
    return await query(sql, sqlParams, false);
  }
  //保存记录
  async saveRecord(data: any) {
    const { recordId, oldRecordId, patientId, bloodDrooping, imgDetail, probability, txt } = data;
    const { RBC, HGB, HCT, MCV, MCH, MCHC, RDW
  } = bloodDrooping;
  const userId = this.ctx.state.user.userId;
  const departmentId = this.ctx.state.user.departmentId;
  const createdAt = new Date();
    return transaction(async conn => {
    const sql = `
        UPDATE patient SET last_visit_date = ? WHERE id = ?;
        `;
    const sqlParams0 = [createdAt, patientId];
    await query(sql, sqlParams0, false, conn);
    const recordsql =
      'INSERT INTO record (id,old_id, user_id,department_id,patient_id, created_at) VALUES (?, ?, ?, ?, ?, ?)';
    const sqlParams = [recordId, oldRecordId, userId, departmentId, patientId, createdAt];
    await query(recordsql, sqlParams, false, conn);
    const bloodDroopingsql =
      `INSERT INTO blood_drooping (record_id, patient_id, RBC, HGB, HCT, MCV, MCH, MCHC, \`RDW-SD\`, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`;
    const sqlParams2 = [recordId, patientId, RBC, HGB, HCT, MCV, MCH, MCHC, RDW, createdAt];
    await query(bloodDroopingsql, sqlParams2, false, conn);
    const imgsql = `INSERT INTO img (id, record_id, link, total, created_at) VALUES (?, ?, ?, ?, ?)`;
    for (const img of imgDetail) {
      const imgId = uuidv7();
      const sqlParams3 = [imgId, recordId, img.originalImageUrl, img.total, createdAt];
      await query(imgsql, sqlParams3, false, conn);
      const imgDetailsql = `INSERT INTO img_detail (id, img_id, type, quantity,percentage,link, created_at) VALUES (?, ?, ?, ?, ?,?, ?)`;
      for (const detail of img.details) {
        const sqlParams4 = [uuidv7(), imgId, detail.type, detail.quantity, detail.percentage, detail.imgPath, createdAt];
        await query(imgDetailsql, sqlParams4, false, conn);
      }
    }
    const probabilitysql = `INSERT INTO report (record_id, outcome, text, created_at) VALUES (?, ?, ?, ?)`;
    const sqlParams5 = [recordId, probability, txt, createdAt];
    await query(probabilitysql, sqlParams5, false, conn);
    const sql2 = `
        INSERT INTO department_daily_statistics (department_id, date, new_records)
            VALUES (?,CURDATE(), 1)
            ON DUPLICATE KEY UPDATE new_records = new_records + 1;`;
    const sqlParams6 = [departmentId];
    await query(sql2, sqlParams6, false, conn);
    if (probability.地中海贫血 > 0.5) {
      const sql3 = `
        INSERT INTO department_daily_statistics (department_id, date, new_diagnoseds)
            VALUES (?,CURDATE(), 1)
            ON DUPLICATE KEY UPDATE new_diagnoseds = new_diagnoseds + 1;`;
      const sqlParams7 = [departmentId];
      await query(sql3, sqlParams7, false, conn);
    }
    await this.cache.del(`departmentPatient:${departmentId}`);
    return true;
  })
  };
}
